/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package AccesoADatos;

import Logica.Usuario;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author LiN
 */
public class ServicioPermisoTableSpace extends Servicio{
    private String user;
    private String pass;
    private String ip;
    private String port;
    private String db;
    
    //Consultas
    private static String GET_TABLES_USER = "select TABLE_NAME,OWNER from all_tables where owner!='SYS' and owner!='SYSTEM' and owner!='OUTLN' and owner!='TSMSYS' and owner!='DBSNMP' and owner!='CTXSYS' and owner!='XDB' and owner!='MDSYS' and owner!='HR' and owner!='FLOWS_FILES' and owner!='FLOWS_020100'";
    private static String GET_TABLES="SELECT table_name, owner FROM dba_tables";
    private static String GET_TABLE_COLUMNS="select column_name from all_tab_columns where table_name=?";
    private static String CREATE_ROLE="create role ";
    private static String DELETE_ROLE="drop role ";
    private static String GRANT_TABLE="grant ";
    private static String GRANT_COLUMN="grant ";

    
    public ServicioPermisoTableSpace(String user,String pass,String ip,String port,String db){
        this.user=user;
        this.pass=pass;
        this.ip=ip;
        this.port=port;
        this.db=db;
    }
    
    public boolean grantPrivilege(String privilege,String role) throws SQLException, ClassNotFoundException{
        boolean result=true;
        
        conectar(user,pass,ip,port,db);
        PreparedStatement pstmt=null;
        pstmt=conexion.prepareStatement(GRANT_COLUMN+privilege+" to "+role);
        result=pstmt.execute();
        
        if(pstmt!=null){
            pstmt.close();
            
        }  
        
        desconectar();
        return result;
    }
    
    public boolean grantPrivilegeC(String privilege,String table,String column,String role) throws SQLException, ClassNotFoundException{
        boolean result=true;
        
        conectar(user,pass,ip,port,db);
        PreparedStatement pstmt=null;
        pstmt=conexion.prepareStatement(GRANT_COLUMN+privilege+" on "+table+"("+column+") "+" to "+role);
        result=pstmt.execute();
        
        if(pstmt!=null){
            pstmt.close();
            
        }  
        
        desconectar();
        return result;
    }
    
    public boolean grantPrivilegeT(String privilege,String table,String role) throws SQLException, ClassNotFoundException{
        boolean result=true;
        
        conectar(user,pass,ip,port,db);
        PreparedStatement pstmt=null;
        pstmt=conexion.prepareStatement(GRANT_TABLE+privilege+" on "+table+" to "+role);
        result=pstmt.execute();
        
        if(pstmt!=null){
            pstmt.close();
            
        }  
        
        desconectar();
        return result;
    }
    
    public boolean dropRole(String role) throws SQLException, ClassNotFoundException{
        boolean result=true;
        
        conectar(user,pass,ip,port,db);
        PreparedStatement pstmt=null;
        
        pstmt=this.conexion.prepareStatement(DELETE_ROLE+role);
        result=pstmt.execute();
        
        if(pstmt!=null){
            pstmt.close();
            
        }  
        desconectar();
        return result;
    }
    public boolean createRole(String role) throws SQLException, ClassNotFoundException{
        boolean result=true;
        
        conectar(user, pass,ip,port,db);
        PreparedStatement pstmt=null;
        
        pstmt=this.conexion.prepareStatement(CREATE_ROLE+role);
        result=pstmt.execute();
        
        if(pstmt!=null){
            pstmt.close();
            
        }  
        desconectar();
        return result;
    }
    
    public Collection listarTablas() throws GlobalException, NoDataException{      
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        Statement stmt = null;
        try{
            stmt = conexion.createStatement();      
            rs = stmt.executeQuery(GET_TABLES_USER);
             while (rs.next()) {
                 String aux = rs.getString("table_name");
                 coleccion.add(aux);
                 aux=rs.getString("owner");
                 coleccion.add(aux);
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
    public Collection listarColumnas(String nombreTabla)throws GlobalException, NoDataException{
        try {
            conectar(user, pass,ip,port,db);       
        }
        catch(ClassNotFoundException ex)
        {
            throw new GlobalException("No se ha localizado el Driver");
        }
        
        catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }      
        
        ResultSet rs=null;
        ArrayList coleccion= new ArrayList();
        PreparedStatement stmt = null;
        try{
            stmt = conexion.prepareStatement(GET_TABLE_COLUMNS);
            stmt.setString(1, nombreTabla);
            rs = stmt.executeQuery();
             while (rs.next()) {
                 String aux = rs.getString("column_name");
                 coleccion.add(aux);
            }
        } catch (SQLException e) {
          e.printStackTrace();
          
       throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
}
